﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Model;
using BLL;
using Webdiyer.WebControls.Mvc;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using System.Web.Security;
using System.Data.OleDb;
using System.Transactions;
namespace Examination.Controllers
{
    public class TextController : Controller
    {
        //
        // GET: /Text/
        /// <summary>
        /// 实例化
        /// </summary>
        TextBLL textbll = new TextBLL();
        Function fun = new Function();
		CompanyBLL bll = new CompanyBLL();
		string ExcelName;
        /// <summary>
        /// 考试题显示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult Index(string name)
        {

            List<TextModel> list = textbll.ShowText(name);
            Session["cid"] = list[0].CompanyId;
            return View(list);
        }
		/// <summary>
		/// 所有公司
		/// </summary>
		/// <param name="pageindex"></param>
		/// <returns></returns>

		public ActionResult Allcompany(int pageindex = 1)
		{
			
			int pagesize = 8;
			PagedList<CompanyModel> list = bll.ShowCompany().ToPagedList(pageindex, pagesize);

			return View(list);
		}

        /// <summary>
        /// 表导入数据库
        /// </summary>
        /// <param name="filebase"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult Show()
        {
			ViewBag.CompanyId = new SelectList(bll.ShowCompany(), "CompanyId", "CompanyName");
			return PartialView();
        }
        [HttpPost]

		public ActionResult Show(HttpPostedFileBase filebase, int CompanyId)
        {
            HttpPostedFileBase file = Request.Files["files"];
            string FileName;
            string savePath;
            if (file == null || file.ContentLength <= 0)
            {
                ViewBag.error = "文件不能为空";
                return View();
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串

                FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
				CompanyModel model = fun.CompanyModel.Find(CompanyId);
				ExcelName =model.CompanyName+"-" + FileName;
                if (!FileType.Contains(fileEx))
                {
                    ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
                    return View();
                }
                if (filesize >= Maxsize)
                {
                    ViewBag.error = "上传文件超过4M，不能上传";
                    return View();
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "Excel/";
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
            }

            //string result = string.Empty;
            string strConn;
            strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
            DataSet myDataSet = new DataSet();
            try
            {
                myCommand.Fill(myDataSet, "ExcelInfo");
            }
            catch (Exception ex)
            {
                ViewBag.error = ex.Message;
                return View();
            }
            DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();

            //引用事务机制，出错时，事物回滚
            using (TransactionScope transaction = new TransactionScope())
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    
                    TextModel model = new TextModel();
                  
                    model.TName = table.Rows[i][0].ToString();
                    model.TAnswer = table.Rows[i][1].ToString();
					model.CompanyId = CompanyId;
					model.ExcelName = ExcelName;
                   fun.TextModel.Add(model);
                    fun.SaveChanges();
                }
                transaction.Complete();
            }
            ViewBag.error = "导入成功";
            System.Threading.Thread.Sleep(2000);
            return Content("<script>alert('数据导入成功！');location.href='/Text/Show'</script>");
        }




       /// <summary>
       /// 数据库导出
       /// </summary>
       /// <param name="strGUID"></param>
       /// <returns></returns>

        public ActionResult DataTableToExcel()
        {

            return View();
        }
        [HttpPost]
        public ActionResult DataTableToExcel(string strGUID)
        {
			if(strGUID =="")
			{

				return Content("<script>alert('至少选择一项');location.href='/Text/Index/" + Session["cid"] + "'</script>");
			}
		else
	{
		string[] GUID = Request.Form["strGUID"].Split(',');



		//这个是读取要导出的列表，逻辑要自己写的
		DataTable dt = new DataTable();
		dt.Columns.Add("序号");
		dt.Columns.Add("题目");
		dt.Columns.Add("答案");
		foreach (var aa in GUID)
		{
			if (aa != null && aa != "")
			{
				int id = Convert.ToInt32(aa);
				//List<User> list = db.user.Where(p => p.Uid == id).ToList();
				TextModel model = fun.TextModel.Find(id);
				dt.Rows.Add(model.Tid, model.TName, model.TAnswer);
			}
		}




		System.Web.UI.WebControls.DataGrid dgExport = null;

		// 当前对话 

		System.Web.HttpContext curContext = System.Web.HttpContext.Current;

		// IO用于导出并返回excel文件 

		System.IO.StringWriter strWriter = null;

		System.Web.UI.HtmlTextWriter htmlWriter = null;

		string filename = DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + "_"

		+ DateTime.Now.Hour + "_" + DateTime.Now.Minute;

		byte[] str = null;



		if (dt != null)
		{

			// 设置编码和附件格式

			curContext.Response.Charset = "GB2312";

			Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");

			curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文

			curContext.Response.ContentType = "application/vnd.ms-excel";

			//System.Text.Encoding.UTF8;

			// 导出excel文件 

			strWriter = new System.IO.StringWriter();

			htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);



			//// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid 

			dgExport = new System.Web.UI.WebControls.DataGrid();

			dgExport.DataSource = dt.DefaultView;

			dgExport.AllowPaging = false;

			dgExport.DataBind();

			dgExport.RenderControl(htmlWriter);

			// 返回客户端 

			str = System.Text.Encoding.UTF8.GetBytes(strWriter.ToString());

		}

		return File(str, "attachment;filename=" + filename + ".xls");

	}
	}
		/// <summary>
		/// 查询
		/// </summary>
		/// <param name="CompanyName"></param>
		/// <param name="index"></param>
		/// <returns></returns>
		[HttpGet]


		public ActionResult SrechCompany(string CompanyName, int index = 1)
		{
			int size = 8;
			PagedList<CompanyModel> list = bll.SreachCompany(CompanyName).ToPagedList(index, size);
			return View("AllCompany", list);

		}
        /// <summary>
        /// 显示所有试题的试卷
        /// </summary>
        /// <returns></returns>
        public ActionResult ShowExcelName(int id)
        {
            Session["Cids"] = id;
            return View();
        }
    }
}
